<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="zh-CN" lang="zh-CN">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>

<title>Active Record and PostgreSQL — Ruby on Rails Guides</title>
<link rel="stylesheet" type="text/css" href="stylesheets/style.css" />
<link rel="stylesheet" type="text/css" href="stylesheets/print.css" media="print" />

<link rel="stylesheet" type="text/css" href="stylesheets/syntaxhighlighter/shCore.css" />
<link rel="stylesheet" type="text/css" href="stylesheets/syntaxhighlighter/shThemeRailsGuides.css" />

<link rel="stylesheet" type="text/css" href="stylesheets/fixes.css" />

<link href="images/favicon.ico" rel="shortcut icon" type="image/x-icon" />
</head>
<body class="guide">
  <div id="topNav">
    <div class="wrapper">
      <strong class="more-info-label">更多内容 <a href="http://rubyonrails.org/">rubyonrails.org:</a> </strong>
      <span class="red-button more-info-button">
        更多内容
      </span>
      <ul class="more-info-links s-hidden">
        <li class="more-info"><a href="http://weblog.rubyonrails.org/">博客</a></li>
        <li class="more-info"><a href="http://guides.rubyonrails.org/">指南</a></li>
        <li class="more-info"><a href="http://api.rubyonrails.org/">API</a></li>
        <li class="more-info"><a href="http://stackoverflow.com/questions/tagged/ruby-on-rails">提问</a></li>
        <li class="more-info"><a href="https://github.com/rails/rails">到 GitHub 贡献</a></li>
        <li class="more-info"><a href="https://ruby-china.org/">Ruby China 社区</a></li>
      </ul>
    </div>
  </div>
  <div id="header">
    <div class="wrapper clearfix">
      <h1><a href="index.html" title="返回首页">Rails 指南</a></h1>
      <ul class="nav">
        <li><a class="nav-item" href="index.html">首页</a></li>
        <li class="guides-index guides-index-large">
          <a href="index.html" id="guidesMenu" class="guides-index-item nav-item">指南索引</a>
          <div id="guides" class="clearfix" style="display: none;">
            <hr />
              <dl class="L">
                <dt>新手入门</dt>
                <dd><a href="getting_started.html">Rails 入门</a></dd>
                <dt>模型</dt>
                <dd><a href="active_record_basics.html">Active Record 基础</a></dd>
                <dd><a href="active_record_migrations.html">Active Record 迁移</a></dd>
                <dd><a href="active_record_validations.html">Active Record 数据验证</a></dd>
                <dd><a href="active_record_callbacks.html">Active Record 回调</a></dd>
                <dd><a href="association_basics.html">Active Record 关联</a></dd>
                <dd><a href="active_record_querying.html">Active Record 查询接口</a></dd>
                <dt>视图</dt>
                <dd><a href="layouts_and_rendering.html">Rails 布局和视图渲染</a></dd>
                <dd><a href="form_helpers.html">Action View 表单辅助方法</a></dd>
                <dt>控制器</dt>
                <dd><a href="action_controller_overview.html">Action Controller 概览</a></dd>
                <dd><a href="routing.html">Rails 路由全解</a></dd>
              </dl>
              <dl class="R">
                <dt>深入探索</dt>
                <dd><a href="active_support_core_extensions.html">Active Support 核心扩展</a></dd>
                <dd><a href="i18n.html">Rails 国际化 API</a></dd>
                <dd><a href="action_mailer_basics.html">Action Mailer 基础</a></dd>
                <dd><a href="active_job_basics.html">Active Job 基础</a></dd>
                <dd><a href="testing.html">Rails 应用测试指南</a></dd>
                <dd><a href="security.html">Ruby on Rails 安全指南</a></dd>
                <dd><a href="debugging_rails_applications.html">调试 Rails 应用</a></dd>
                <dd><a href="configuring.html">配置 Rails 应用</a></dd>
                <dd><a href="command_line.html">Rails 命令行</a></dd>
                <dd><a href="asset_pipeline.html">Asset Pipeline</a></dd>
                <dd><a href="working_with_javascript_in_rails.html">在 Rails 中使用 JavaScript</a></dd>
                <dd><a href="autoloading_and_reloading_constants.html">自动加载和重新加载常量</a></dd>
                <dd><a href="caching_with_rails.html">Rails 缓存概览</a></dd>
                <dd><a href="api_app.html">使用 Rails 开发只提供 API 的应用</a></dd>
                <dd><a href="action_cable_overview.html">Action Cable 概览</a></dd>
                <dt>扩展 Rails</dt>
                <dd><a href="rails_on_rack.html">Rails on Rack</a></dd>
                <dd><a href="generators.html">创建及定制 Rails 生成器和模板</a></dd>
                <dt>为 Ruby on Rails 做贡献</dt>
                <dd><a href="contributing_to_ruby_on_rails.html">为 Ruby on Rails 做贡献</a></dd>
                <dd><a href="api_documentation_guidelines.html">API 文档指导方针</a></dd>
                <dd><a href="ruby_on_rails_guides_guidelines.html">Ruby on Rails 指南指导方针</a></dd>
                <dt>维护方针</dt>
                <dd><a href="maintenance_policy.html">Ruby on Rails 的维护方针</a></dd>
                <dt>发布记</dt>
                <dd><a href="upgrading_ruby_on_rails.html">Ruby on Rails 升级指南</a></dd>
                <dd><a href="5_0_release_notes.html">Ruby on Rails 5.0 发布记</a></dd>
                <dd><a href="4_2_release_notes.html">Ruby on Rails 4.2 发布记</a></dd>
                <dd><a href="4_1_release_notes.html">Ruby on Rails 4.1 发布记</a></dd>
                <dd><a href="4_0_release_notes.html">Ruby on Rails 4.0 发布记</a></dd>
                <dd><a href="3_2_release_notes.html">Ruby on Rails 3.2 发布记</a></dd>
                <dd><a href="3_1_release_notes.html">Ruby on Rails 3.1 发布记</a></dd>
                <dd><a href="3_0_release_notes.html">Ruby on Rails 3.0 发布记</a></dd>
                <dd><a href="2_3_release_notes.html">Ruby on Rails 2.3 发布记</a></dd>
                <dd><a href="2_2_release_notes.html">Ruby on Rails 2.2 发布记</a></dd>
              </dl>
          </div>
        </li>
        <li><a class="nav-item" href="contributing_to_ruby_on_rails.html">贡献</a></li>
        <li><a class="nav-item" href="credits.html">感谢</a></li>
        <li class="guides-index guides-index-small">
          <select class="guides-index-item nav-item">
            <option value="index.html">指南索引</option>
              <optgroup label="新手入门">
                  <option value="getting_started.html">Rails 入门</option>
              </optgroup>
              <optgroup label="模型">
                  <option value="active_record_basics.html">Active Record 基础</option>
                  <option value="active_record_migrations.html">Active Record 迁移</option>
                  <option value="active_record_validations.html">Active Record 数据验证</option>
                  <option value="active_record_callbacks.html">Active Record 回调</option>
                  <option value="association_basics.html">Active Record 关联</option>
                  <option value="active_record_querying.html">Active Record 查询接口</option>
              </optgroup>
              <optgroup label="视图">
                  <option value="layouts_and_rendering.html">Rails 布局和视图渲染</option>
                  <option value="form_helpers.html">Action View 表单辅助方法</option>
              </optgroup>
              <optgroup label="控制器">
                  <option value="action_controller_overview.html">Action Controller 概览</option>
                  <option value="routing.html">Rails 路由全解</option>
              </optgroup>
              <optgroup label="深入探索">
                  <option value="active_support_core_extensions.html">Active Support 核心扩展</option>
                  <option value="i18n.html">Rails 国际化 API</option>
                  <option value="action_mailer_basics.html">Action Mailer 基础</option>
                  <option value="active_job_basics.html">Active Job 基础</option>
                  <option value="testing.html">Rails 应用测试指南</option>
                  <option value="security.html">Ruby on Rails 安全指南</option>
                  <option value="debugging_rails_applications.html">调试 Rails 应用</option>
                  <option value="configuring.html">配置 Rails 应用</option>
                  <option value="command_line.html">Rails 命令行</option>
                  <option value="asset_pipeline.html">Asset Pipeline</option>
                  <option value="working_with_javascript_in_rails.html">在 Rails 中使用 JavaScript</option>
                  <option value="autoloading_and_reloading_constants.html">自动加载和重新加载常量</option>
                  <option value="caching_with_rails.html">Rails 缓存概览</option>
                  <option value="api_app.html">使用 Rails 开发只提供 API 的应用</option>
                  <option value="action_cable_overview.html">Action Cable 概览</option>
              </optgroup>
              <optgroup label="扩展 Rails">
                  <option value="rails_on_rack.html">Rails on Rack</option>
                  <option value="generators.html">创建及定制 Rails 生成器和模板</option>
              </optgroup>
              <optgroup label="为 Ruby on Rails 做贡献">
                  <option value="contributing_to_ruby_on_rails.html">为 Ruby on Rails 做贡献</option>
                  <option value="api_documentation_guidelines.html">API 文档指导方针</option>
                  <option value="ruby_on_rails_guides_guidelines.html">Ruby on Rails 指南指导方针</option>
              </optgroup>
              <optgroup label="维护方针">
                  <option value="maintenance_policy.html">Ruby on Rails 的维护方针</option>
              </optgroup>
              <optgroup label="发布记">
                  <option value="upgrading_ruby_on_rails.html">Ruby on Rails 升级指南</option>
                  <option value="5_0_release_notes.html">Ruby on Rails 5.0 发布记</option>
                  <option value="4_2_release_notes.html">Ruby on Rails 4.2 发布记</option>
                  <option value="4_1_release_notes.html">Ruby on Rails 4.1 发布记</option>
                  <option value="4_0_release_notes.html">Ruby on Rails 4.0 发布记</option>
                  <option value="3_2_release_notes.html">Ruby on Rails 3.2 发布记</option>
                  <option value="3_1_release_notes.html">Ruby on Rails 3.1 发布记</option>
                  <option value="3_0_release_notes.html">Ruby on Rails 3.0 发布记</option>
                  <option value="2_3_release_notes.html">Ruby on Rails 2.3 发布记</option>
                  <option value="2_2_release_notes.html">Ruby on Rails 2.2 发布记</option>
              </optgroup>
          </select>
        </li>
      </ul>
    </div>
  </div>
  <hr class="hide" />

  <div id="feature">
    <div class="wrapper">
      <h2>Active Record and PostgreSQL</h2><p>This guide covers PostgreSQL specific usage of Active Record.</p><p>After reading this guide, you will know:</p>
<ul>
<li>How to use PostgreSQL&#39;s datatypes.</li>
<li>How to use UUID primary keys.</li>
<li>How to implement full text search with PostgreSQL.</li>
<li>How to back your Active Record models with database views.</li>
</ul>


              <div id="subCol">
          <h3 class="chapter"><img src="images/chapters_icon.gif" alt="" />目录</h3>
          <ol class="chapters">
<li>
<a href="#datatypes">Datatypes</a>

<ul>
<li><a href="#bytea">Bytea</a></li>
<li><a href="#array">Array</a></li>
<li><a href="#hstore">Hstore</a></li>
<li><a href="#json">JSON</a></li>
<li><a href="#range-types">Range Types</a></li>
<li><a href="#composite-types">Composite Types</a></li>
<li><a href="#enumerated-types">Enumerated Types</a></li>
<li><a href="#uuid">UUID</a></li>
<li><a href="#bit-string-types">Bit String Types</a></li>
<li><a href="#network-address-types">Network Address Types</a></li>
<li><a href="#geometric-types">Geometric Types</a></li>
</ul>
</li>
<li><a href="#uuid-primary-keys">UUID Primary Keys</a></li>
<li><a href="#full-text-search">Full Text Search</a></li>
<li><a href="#database-views">Database Views</a></li>
</ol>

        </div>

    </div>
  </div>

  <div id="container">
    <div class="wrapper">
      <div id="mainCol">
        <p>In order to use the PostgreSQL adapter you need to have at least version 9.1
installed. Older versions are not supported.</p><p>To get started with PostgreSQL have a look at the
<a href="configuring.html#configuring-a-postgresql-database">configuring Rails guide</a>.
It describes how to properly setup Active Record for PostgreSQL.</p><h3 id="datatypes">1 Datatypes</h3><p>PostgreSQL offers a number of specific datatypes. Following is a list of types,
that are supported by the PostgreSQL adapter.</p><h4 id="bytea">1.1 Bytea</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-binary.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/functions-binarystring.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20140207133952_create_documents.rb
create_table :documents do |t|
  t.binary 'payload'
end

# app/models/document.rb
class Document &lt; ApplicationRecord
end

# Usage
data = File.read(Rails.root + "tmp/output.pdf")
Document.create payload: data

</pre>
</div>
<h4 id="array">1.2 Array</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/arrays.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/functions-array.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20140207133952_create_books.rb
create_table :books do |t|
  t.string 'title'
  t.string 'tags', array: true
  t.integer 'ratings', array: true
end
add_index :books, :tags, using: 'gin'
add_index :books, :ratings, using: 'gin'

# app/models/book.rb
class Book &lt; ApplicationRecord
end

# Usage
Book.create title: "Brave New World",
            tags: ["fantasy", "fiction"],
            ratings: [4, 5]

## Books for a single tag
Book.where("'fantasy' = ANY (tags)")

## Books for multiple tags
Book.where("tags @&gt; ARRAY[?]::varchar[]", ["fantasy", "fiction"])

## Books with 3 or more ratings
Book.where("array_length(ratings, 1) &gt;= 3")

</pre>
</div>
<h4 id="hstore">1.3 Hstore</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/hstore.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/hstore.html#AEN167712">functions and operators</a></li>
</ul>
<div class="note"><p>You need to enable the <code>hstore</code> extension to use hstore.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131009135255_create_profiles.rb
ActiveRecord::Schema.define do
  enable_extension 'hstore' unless extension_enabled?('hstore')
  create_table :profiles do |t|
    t.hstore 'settings'
  end
end

# app/models/profile.rb
class Profile &lt; ApplicationRecord
end

# Usage
Profile.create(settings: { "color" =&gt; "blue", "resolution" =&gt; "800x600" })

profile = Profile.first
profile.settings # =&gt; {"color"=&gt;"blue", "resolution"=&gt;"800x600"}

profile.settings = {"color" =&gt; "yellow", "resolution" =&gt; "1280x1024"}
profile.save!

Profile.where("settings-&gt;'color' = ?", "yellow")
#=&gt; #&lt;ActiveRecord::Relation [#&lt;Profile id: 1, settings: {"color"=&gt;"yellow", "resolution"=&gt;"1280x1024"}&gt;]&gt;

</pre>
</div>
<h4 id="json">1.4 JSON</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-json.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/functions-json.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_events.rb
create_table :events do |t|
  t.json 'payload'
end

# app/models/event.rb
class Event &lt; ApplicationRecord
end

# Usage
Event.create(payload: { kind: "user_renamed", change: ["jack", "john"]})

event = Event.first
event.payload # =&gt; {"kind"=&gt;"user_renamed", "change"=&gt;["jack", "john"]}

## Query based on JSON document
# The -&gt; operator returns the original JSON type (which might be an object), whereas -&gt;&gt; returns text
Event.where("payload-&gt;&gt;'kind' = ?", "user_renamed")

</pre>
</div>
<h4 id="range-types">1.5 Range Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/rangetypes.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/functions-range.html">functions and operators</a></li>
</ul>
<p>This type is mapped to Ruby <a href="http://www.ruby-doc.org/core-2.2.2/Range.html"><code>Range</code></a> objects.</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20130923065404_create_events.rb
create_table :events do |t|
  t.daterange 'duration'
end

# app/models/event.rb
class Event &lt; ApplicationRecord
end

# Usage
Event.create(duration: Date.new(2014, 2, 11)..Date.new(2014, 2, 12))

event = Event.first
event.duration # =&gt; Tue, 11 Feb 2014...Thu, 13 Feb 2014

## All Events on a given date
Event.where("duration @&gt; ?::date", Date.new(2014, 2, 12))

## Working with range bounds
event = Event.
  select("lower(duration) AS starts_at").
  select("upper(duration) AS ends_at").first

event.starts_at # =&gt; Tue, 11 Feb 2014
event.ends_at # =&gt; Thu, 13 Feb 2014

</pre>
</div>
<h4 id="composite-types">1.6 Composite Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/rowtypes.html">type definition</a></li>
</ul>
<p>Currently there is no special support for composite types. They are mapped to
normal text columns:</p><div class="code_container">
<pre class="brush: sql; gutter: false; toolbar: false">
CREATE TYPE full_address AS
(
  city VARCHAR(90),
  street VARCHAR(90)
);

</pre>
</div>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20140207133952_create_contacts.rb
execute &lt;&lt;-SQL
 CREATE TYPE full_address AS
 (
   city VARCHAR(90),
   street VARCHAR(90)
 );
SQL
create_table :contacts do |t|
  t.column :address, :full_address
end

# app/models/contact.rb
class Contact &lt; ApplicationRecord
end

# Usage
Contact.create address: "(Paris,Champs-Élysées)"
contact = Contact.first
contact.address # =&gt; "(Paris,Champs-Élysées)"
contact.address = "(Paris,Rue Basse)"
contact.save!

</pre>
</div>
<h4 id="enumerated-types">1.7 Enumerated Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-enum.html">type definition</a></li>
</ul>
<p>Currently there is no special support for enumerated types. They are mapped as
normal text columns:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_articles.rb
def up
  execute &lt;&lt;-SQL
    CREATE TYPE article_status AS ENUM ('draft', 'published');
  SQL
  create_table :articles do |t|
    t.column :status, :article_status
  end
end

# NOTE: It's important to drop table before dropping enum.
def down
  drop_table :articles

  execute &lt;&lt;-SQL
    DROP TYPE article_status;
  SQL
end

# app/models/article.rb
class Article &lt; ApplicationRecord
end

# Usage
Article.create status: "draft"
article = Article.first
article.status # =&gt; "draft"

article.status = "published"
article.save!

</pre>
</div>
<p>To add a new value before/after existing one you should use <a href="http://www.postgresql.org/docs/current/static/sql-altertype.html">ALTER TYPE</a>:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20150720144913_add_new_state_to_articles.rb
# NOTE: ALTER TYPE ... ADD VALUE cannot be executed inside of a transaction block so here we are using disable_ddl_transaction!
disable_ddl_transaction!

def up
  execute &lt;&lt;-SQL
    ALTER TYPE article_status ADD VALUE IF NOT EXISTS 'archived' AFTER 'published';
  SQL
end

</pre>
</div>
<div class="note"><p>ENUM values can't be dropped currently. You can read why <a href="http://www.postgresql.org/message-id/29F36C7C98AB09499B1A209D48EAA615B7653DBC8A@mail2a.alliedtesting.com">here</a>.</p></div><p>Hint: to show all the values of the all enums you have, you should call this query in <code>bin/rails db</code> or <code>psql</code> console:</p><div class="code_container">
<pre class="brush: sql; gutter: false; toolbar: false">
SELECT n.nspname AS enum_schema,
       t.typname AS enum_name,
       e.enumlabel AS enum_value
  FROM pg_type t
      JOIN pg_enum e ON t.oid = e.enumtypid
      JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace

</pre>
</div>
<h4 id="uuid">1.8 UUID</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-uuid.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/pgcrypto.html#AEN159361">pgcrypto generator function</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/uuid-ossp.html">uuid-ossp generator functions</a></li>
</ul>
<div class="note"><p>You need to enable the <code>pgcrypto</code> (only PostgreSQL &gt;= 9.4) or <code>uuid-ossp</code>
extension to use uuid.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_revisions.rb
create_table :revisions do |t|
  t.uuid :identifier
end

# app/models/revision.rb
class Revision &lt; ApplicationRecord
end

# Usage
Revision.create identifier: "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"

revision = Revision.first
revision.identifier # =&gt; "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"

</pre>
</div>
<p>You can use <code>uuid</code> type to define references in migrations:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20150418012400_create_blog.rb
enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
create_table :posts, id: :uuid, default: 'gen_random_uuid()'

create_table :comments, id: :uuid, default: 'gen_random_uuid()' do |t|
  # t.belongs_to :post, type: :uuid
  t.references :post, type: :uuid
end

# app/models/post.rb
class Post &lt; ApplicationRecord
  has_many :comments
end

# app/models/comment.rb
class Comment &lt; ApplicationRecord
  belongs_to :post
end

</pre>
</div>
<p>See <a href="#uuid-primary-keys">this section</a> for more details on using UUIDs as primary key.</p><h4 id="bit-string-types">1.9 Bit String Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-bit.html">type definition</a></li>
<li><a href="http://www.postgresql.org/docs/current/static/functions-bitstring.html">functions and operators</a></li>
</ul>
<div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_users.rb
create_table :users, force: true do |t|
  t.column :settings, "bit(8)"
end

# app/models/device.rb
class User &lt; ApplicationRecord
end

# Usage
User.create settings: "01010011"
user = User.first
user.settings # =&gt; "01010011"
user.settings = "0xAF"
user.settings # =&gt; 10101111
user.save!

</pre>
</div>
<h4 id="network-address-types">1.10 Network Address Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-net-types.html">type definition</a></li>
</ul>
<p>The types <code>inet</code> and <code>cidr</code> are mapped to Ruby
<a href="http://www.ruby-doc.org/stdlib-2.2.2/libdoc/ipaddr/rdoc/IPAddr.html"><code>IPAddr</code></a>
objects. The <code>macaddr</code> type is mapped to normal text.</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20140508144913_create_devices.rb
create_table(:devices, force: true) do |t|
  t.inet 'ip'
  t.cidr 'network'
  t.macaddr 'address'
end

# app/models/device.rb
class Device &lt; ApplicationRecord
end

# Usage
macbook = Device.create(ip: "192.168.1.12",
                        network: "192.168.2.0/24",
                        address: "32:01:16:6d:05:ef")

macbook.ip
# =&gt; #&lt;IPAddr: IPv4:192.168.1.12/255.255.255.255&gt;

macbook.network
# =&gt; #&lt;IPAddr: IPv4:192.168.2.0/255.255.255.0&gt;

macbook.address
# =&gt; "32:01:16:6d:05:ef"

</pre>
</div>
<h4 id="geometric-types">1.11 Geometric Types</h4>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/datatype-geometric.html">type definition</a></li>
</ul>
<p>All geometric types, with the exception of <code>points</code> are mapped to normal text.
A point is casted to an array containing <code>x</code> and <code>y</code> coordinates.</p><h3 id="uuid-primary-keys">2 UUID Primary Keys</h3><div class="note"><p>You need to enable the <code>pgcrypto</code> (only PostgreSQL &gt;= 9.4) or <code>uuid-ossp</code>
extension to generate random UUIDs.</p></div><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_devices.rb
enable_extension 'pgcrypto' unless extension_enabled?('pgcrypto')
create_table :devices, id: :uuid, default: 'gen_random_uuid()' do |t|
  t.string :kind
end

# app/models/device.rb
class Device &lt; ApplicationRecord
end

# Usage
device = Device.create
device.id # =&gt; "814865cd-5a1d-4771-9306-4268f188fe9e"

</pre>
</div>
<div class="note"><p><code>uuid_generate_v4()</code> (from <code>uuid-ossp</code>) is assumed if no <code>:default</code> option was
passed to <code>create_table</code>.</p></div><h3 id="full-text-search">3 Full Text Search</h3><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_documents.rb
create_table :documents do |t|
  t.string 'title'
  t.string 'body'
end

execute "CREATE INDEX documents_idx ON documents USING gin(to_tsvector('english', title || ' ' || body));"

# app/models/document.rb
class Document &lt; ApplicationRecord
end

# Usage
Document.create(title: "Cats and Dogs", body: "are nice!")

## all documents matching 'cat &amp; dog'
Document.where("to_tsvector('english', title || ' ' || body) @@ to_tsquery(?)",
                 "cat &amp; dog")

</pre>
</div>
<h3 id="database-views">4 Database Views</h3>
<ul>
<li><a href="http://www.postgresql.org/docs/current/static/sql-createview.html">view creation</a></li>
</ul>
<p>Imagine you need to work with a legacy database containing the following table:</p><div class="code_container">
<pre class="brush: plain; gutter: false; toolbar: false">
rails_pg_guide=# \d "TBL_ART"
                                        Table "public.TBL_ART"
   Column   |            Type             |                         Modifiers
------------+-----------------------------+------------------------------------------------------------
 INT_ID     | integer                     | not null default nextval('"TBL_ART_INT_ID_seq"'::regclass)
 STR_TITLE  | character varying           |
 STR_STAT   | character varying           | default 'draft'::character varying
 DT_PUBL_AT | timestamp without time zone |
 BL_ARCH    | boolean                     | default false
Indexes:
    "TBL_ART_pkey" PRIMARY KEY, btree ("INT_ID")

</pre>
</div>
<p>This table does not follow the Rails conventions at all.
Because simple PostgreSQL views are updateable by default,
we can wrap it as follows:</p><div class="code_container">
<pre class="brush: ruby; gutter: false; toolbar: false">
# db/migrate/20131220144913_create_articles_view.rb
execute &lt;&lt;-SQL
CREATE VIEW articles AS
  SELECT "INT_ID" AS id,
         "STR_TITLE" AS title,
         "STR_STAT" AS status,
         "DT_PUBL_AT" AS published_at,
         "BL_ARCH" AS archived
  FROM "TBL_ART"
  WHERE "BL_ARCH" = 'f'
  SQL

# app/models/article.rb
class Article &lt; ApplicationRecord
  self.primary_key = "id"
  def archive!
    update_attribute :archived, true
  end
end

# Usage
first = Article.create! title: "Winter is coming",
                        status: "published",
                        published_at: 1.year.ago
second = Article.create! title: "Brace yourself",
                         status: "draft",
                         published_at: 1.month.ago

Article.count # =&gt; 1
first.archive!
Article.count # =&gt; 2

</pre>
</div>
<div class="note"><p>This application only cares about non-archived <code>Articles</code>. A view also
allows for conditions so we can exclude the archived <code>Articles</code> directly.</p></div>

        <h3>反馈</h3>
        <p>
          我们鼓励您帮助提高本指南的质量。
        </p>
        <p>
          如果看到如何错字或错误，请反馈给我们。
          您可以阅读我们的<a href="http://edgeguides.rubyonrails.org/contributing_to_ruby_on_rails.html#contributing-to-the-rails-documentation">文档贡献</a>指南。
        </p>
        <p>
          您还可能会发现内容不完整或不是最新版本。
          请添加缺失文档到 master 分支。请先确认 <a href="http://edgeguides.rubyonrails.org">Edge Guides</a> 是否已经修复。
          关于用语约定，请查看<a href="ruby_on_rails_guides_guidelines.html">Ruby on Rails 指南指导</a>。
        </p>
        <p>
          无论什么原因，如果你发现了问题但无法修补它，请<a href="https://github.com/rails/rails/issues">创建 issue</a>。
        </p>
        <p>
          最后，欢迎到 <a href="http://groups.google.com/group/rubyonrails-docs">rubyonrails-docs 邮件列表</a>参与任何有关 Ruby on Rails 文档的讨论。
        </p>
        <h4>中文翻译反馈</h4>
        <p>贡献：<a href="https://github.com/ruby-china/guides">https://github.com/ruby-china/guides</a>。</p>
      </div>
    </div>
  </div>

  <hr class="hide" />
  <div id="footer">
    <div class="wrapper">
      <p>本著作采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/">创作共用 署名-相同方式共享 4.0 国际</a> 授权</p>
<p>“Rails”，“Ruby on Rails”，以及 Rails Logo 为 David Heinemeier Hansson 的商标。版权所有</p>

    </div>
  </div>

  <script type="text/javascript" src="javascripts/jquery.min.js"></script>
  <script type="text/javascript" src="javascripts/responsive-tables.js"></script>
  <script type="text/javascript" src="javascripts/guides.js"></script>
  <script type="text/javascript" src="javascripts/syntaxhighlighter.js"></script>
  <script type="text/javascript">
    syntaxhighlighterConfig = {
      autoLinks: false,
    };
    $(guidesIndex.bind);
  </script>
</body>
</html>
